package dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

import model.CareerDev;
import model.Category;
import model.Employee;
import model.Feedback;
import model.Section;
import model.Title;

public class FeedbackDAO {
	ConnectionDB con;
	ObjectDAO objectDao;
	EmployeeDAO emDao;
	SimpleDateFormat format; 
	
	
	public FeedbackDAO() throws SQLException, ClassNotFoundException{
		con = new ConnectionDB();
		
		objectDao = new ObjectDAO();
		emDao = new EmployeeDAO();
		
		format = new SimpleDateFormat("yyyy-MM-dd");
	}
	
	
	/**
	 * remove a feedback from db
	 * @param feedbackId
	 * @return boolean value to ensure db is updated
	 * @throws SQLException
	 */
	@SuppressWarnings("static-access")
	public boolean  removeFeedback(int feedbackId) throws SQLException {
		PreparedStatement ps;
		try {
			String query = "DELETE Feedback WHERE FeedbackId=?";
			
            ps = con.getConnection().prepareStatement(query);
            ps.setInt(1, feedbackId);

            int success = ps.executeUpdate();
            
            if(success > 0) {
            	con.conn.commit();
            	ps.close();
             	return true;
            }
            else {
            	 ps.close();
             	return false;
            }
            
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionDB.closeConnection();
		}
		return false;
	}
	
	
	/**
	 * add new feed back to db
	 * @param feedback
	 * @return boolean value to ensure db is updated
	 * @throws SQLException
	 */
	@SuppressWarnings("static-access")
	public boolean addFeedback(Feedback feedback) throws SQLException {
		PreparedStatement ps;
		System.out.println(format.format(feedback.getDateMade()));
		try {
			String query = "INSERT INTO Feedback VALUES (?, ?, ?, ?, ?)";
			
            ps = con.getConnection().prepareStatement(query);
            ps.setString(1, feedback.getContent());
            ps.setInt(2, feedback.getOwner().getUserid());
            ps.setInt(3, feedback.getTarget().getUserid());
            ps.setString(4, format.format(feedback.getDateMade()));
            ps.setInt(5, feedback.getSection().getSectionId());

            int success = ps.executeUpdate();
            
            if(success > 0) {
            	con.conn.commit();
                ps.close();
            	return true;
            }
            else  {
            	 ps.close();
             	return false;
            }
            
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionDB.closeConnection();
		}
		return false;
	}
	
	
	
	/**
	 * get a specific feedback
	 * @param feedbackId
	 * @return Feedback
	 * @throws SQLException
	 */
	@SuppressWarnings("static-access")
	public Feedback getAFeedback(int feedbackId) throws SQLException {
		Feedback feedback = null;;
		PreparedStatement ps;
		ResultSet rs;
		
		try {
			String query = "SELECT FeedBackID,FeedContent,UserMade,UserTarget,DateMade," +
					"Feedback.SectionID,Category.CategoryId  FROM Feedback,Category,Section " +
					"WHERE Feedback.SectionID = Section.SectionId AND Section.categoryid = " +
					"Category.CategoryID AND feedbackId = ?";
			
            ps = con.getConnection().prepareStatement(query);
            ps.setInt(1, feedbackId);
            rs = ps.executeQuery();

            if (rs.next()) {
            	Employee target = emDao.getAEmployee(rs.getInt(4));
                Employee owner = emDao.getAEmployee(rs.getInt(3));
                Section section = objectDao.getASection(rs.getInt(6));
                
                Category ca = objectDao.getACategory(rs.getInt(7));
                
                feedback = new Feedback(rs.getInt(1), rs.getString(2), owner, target,
                		rs.getDate(5), ca, section);
            }
            rs.close();
            ps.close();
			
			return feedback;	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionDB.closeConnection();
		}
		return feedback;
	}
	
	
	
	/**
	 * get all feedbacks from db
	 * @return list of Feedback
	 * @throws SQLException
	 */
	public ArrayList<Feedback> listFeedbacks() throws SQLException {
		ArrayList<Feedback> list = new ArrayList<Feedback>();
		Statement st;
		ResultSet rs;
		
		try {
			String query = "SELECT FeedBackID,FeedContent,UserMade,UserTarget,DateMade," +
					"Feedback.SectionID,Category.CategoryId  FROM Feedback,Category,Section " +
					"WHERE Feedback.SectionID = Section.SectionId AND Section.categoryid = " +
					"Category.CategoryID";
			
            st = ConnectionDB.getConnection().createStatement();
            rs = st.executeQuery(query);
            while (rs.next()) {                
                Employee target = emDao.getAEmployee(rs.getInt(4));
                Employee owner = emDao.getAEmployee(rs.getInt(3));
                Section section = objectDao.getASection(rs.getInt(6));
                
                Category ca = objectDao.getACategory(rs.getInt(7));
                
                Feedback fb = new Feedback(rs.getInt(1), rs.getString(2), owner, target,
                		rs.getDate(5), ca, section);
                
            	list.add(fb);
            }
            rs.close();
            st.close();
			return list;	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionDB.closeConnection();
		}
		
		return list;
	}
	
}
